package com.ustadzmenjawab.server;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashSet;
import java.util.List;
import com.ustadzmenjawab.shared.Article;
import com.ustadzmenjawab.shared.SearchResult;
import com.ustadzmenjawab.shared.WebsiteSource;
public class DBAccess {
	protected static HashSet<String> stopWord;
//	static{
//		stopWord = new HashSet<String>();
//		loadStopWord();
//	}
	
	public DBAccess(){
		if(stopWord == null){
			stopWord = new HashSet<String>();
			loadStopWord();
		}
	}
	
	public String getPage(String pageName){
		Connection conn = DBConnFactory.getConnection();
		try {
			PreparedStatement ps = null;
			String sql_get = "Select * from page where page_name = ?";
			ps = conn.prepareStatement(sql_get);
			ps.setString(1, pageName);
			ResultSet rs = ps.executeQuery();
			String content = "";
			while(rs.next()){
				content = rs.getString("content");
			}
			return content;
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	
	public List<String> getInsertedQuery(){
		Connection conn = DBConnFactory.getConnection();
		try {
			PreparedStatement ps = null;
			String sql_get = "Select * from keyword_user";
			ps = conn.prepareStatement(sql_get);
			
			ResultSet rs = ps.executeQuery();
			List<String> content = new ArrayList<String>();
			while(rs.next()){
				content.add( rs.getString("kata_pencarian"));
			}
			return content;
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	
	public List<WebsiteSource> getSourceList(){
		Connection conn = DBConnFactory.getConnection();
		try {
			PreparedStatement ps = null;
			String sql_get = "Select * from source_website";
			ps = conn.prepareStatement(sql_get);

			ResultSet rs = ps.executeQuery();
			List<WebsiteSource> ret = new ArrayList<WebsiteSource>();
			while(rs.next()){
				String name = rs.getString("website_name");
				String url = rs.getString("website_url");
				WebsiteSource w = new WebsiteSource();
				w.setWebsiteName(name);
				w.setWebsiteUrl(url);
				ret.add(w);
			}
			return ret;
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	public void insertArticle(Article article){
			Connection conn = DBConnFactory.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement("insert into ustadz_menjawab(judul, ustadz, artikel, sumber, tahun, url, kategori) Values (?,?,?,?,?,?,?)");
			ps.setString(1, article.getJudul());
			ps.setString(2, article.getUstadz());
			ps.setString(3, article.getArtikel());
			ps.setString(4, article.getSumber());
			ps.setString(5, article.getTahun());
			ps.setString(6, article.getUrl());
			ps.setString(7, article.getKategori());
			ps.executeUpdate();
			ps.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		
	}
	public void updateVisit(String url){
		if(url == null)
			return;
		Connection conn = DBConnFactory.getConnection();
	try {
		int last = 0;
		PreparedStatement ps = null;
		String sql_get = "Select kunjungan from ustadzmenjawabdb_prod where url = ?";
		ps = conn.prepareStatement(sql_get);
		ps.setString(1, url);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			last = rs.getInt(1);
		}
		rs.close();
		ps.close();
		String sql_upd = "Update ustadzmenjawabdb_prod set kunjungan = ? where url = ?";
		ps = conn.prepareStatement(sql_upd);
		ps.setInt(1, last+1);
		ps.setString(2, url);
		ps.executeUpdate();
		ps.close();
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally{
		DBConnFactory.closeConnection(conn);
	}
	
}
	public Article getOneArticle(int articleID){
		Connection conn = DBConnFactory.getConnection();
		try {
			PreparedStatement ps = null;
			String sql_get = "Select * from ustadzmenjawabdb_prod where id = ?";
			ps = conn.prepareStatement(sql_get);
			ps.setInt(1, articleID);
			ResultSet rs = ps.executeQuery();
			Article ret = new Article();
			while(rs.next()){
				ret.setId(rs.getInt("id")) ;
				ret.setJudul(rs.getString("judul"));
				ret.setPertanyaan(rs.getString("pertanyaan"));
				ret.setArtikel(rs.getString("artikel"));
				ret.setKategori(rs.getString("kategori"));
				ret.setUrl(rs.getString("ustadz"));
				ret.setSumber(rs.getString("sumber"));
				ret.setTahun(rs.getString("tahun"));
			}
			return ret;
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	
	public List<SearchResult> searchDBForKeyword(String keyword, String ipAddr){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		try{
			insertUserQuery(keyword,ipAddr,conn);
			String keyword_artikel = cleanStopWord(keyword);
			keyword_artikel = cleanPunctuation(keyword_artikel);
			keyword_artikel = expandQuery(keyword_artikel, conn);
	
			String sql = "{call searchKeywordAllSources(?)}";
			CallableStatement ps = conn.prepareCall(sql);
			
			ps.setString(1, keyword_artikel);
	
			rs = ps.executeQuery();
			List<SearchResult> ls = new ArrayList<SearchResult>();
			
			while(rs.next()){
				SearchResult s = new SearchResult();
				s.setId(rs.getInt("id"));
				s.setTitle(rs.getString("judul"));
				s.setSource(rs.getString("sumber"));
				String bestFragment = FragmentUtil.getBestFragments(keyword_artikel, s.getTitle() + "\n"+ rs.getString("artikel"));
				s.setSummary(bestFragment);
				s.setUrl(rs.getString("url"));
				s.setUstadz(rs.getString("ustadz"));
				s.setOffline(rs.getString("offline"));
				ls.add(s);
			}
			if(ls.size() == 0)
				return null;
			return ls;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	
	
	public List<SearchResult> searchDBForKeywordWithSource(String keyword,String source){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		try{
			String keyword_artikel = cleanStopWord(keyword);
			keyword_artikel = cleanPunctuation(keyword_artikel);
			keyword_artikel = expandQuery(keyword_artikel, conn);
			
			String sql = "{call searchKeywordOneSource(?,?)}";
			CallableStatement ps = conn.prepareCall(sql);
			ps.setString(1, keyword_artikel);
			ps.setString(2, source);
			
			rs = ps.executeQuery();
			List<SearchResult> ls = new ArrayList<SearchResult>();
			
			while(rs.next()){
				SearchResult s = new SearchResult();
				s.setId(rs.getInt("id"));
				s.setTitle(rs.getString("judul"));
				s.setSource(rs.getString("sumber"));
				String bestFragment = FragmentUtil.getBestFragments(keyword_artikel, s.getTitle() + "\n"+ rs.getString("artikel"));
				s.setSummary(bestFragment);
				s.setUrl(rs.getString("url"));
				s.setUstadz(rs.getString("ustadz"));
				s.setOffline(rs.getString("offline"));
				ls.add(s);
				
			}
			if(ls.size() == 0)
				return null;
			return ls;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	
	public List<SearchResult> searchDBForKeyword(String keyword,int start, int size){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		if(size == 0 || start < 0)
			return null;
		try{
			String keyword_artikel = cleanStopWord(keyword);
			keyword_artikel = cleanPunctuation(keyword_artikel);
			keyword_artikel = expandQuery(keyword_artikel, conn);
			
			String sql = "{call searchKeywordAllSourcesPaging(?,?)}";
			CallableStatement ps = conn.prepareCall(sql);
			ps.setString(1, keyword_artikel);
			ps.setInt(2, start);
			rs = ps.executeQuery();
			List<SearchResult> ls = new ArrayList<SearchResult>();
			
			while(rs.next()){
				SearchResult s = new SearchResult();
				s.setId(rs.getInt("id"));
				s.setTitle(rs.getString("judul"));
				s.setSource(rs.getString("sumber"));
				String bestFragment = FragmentUtil.getBestFragments(keyword_artikel, s.getTitle() + "\n"+ rs.getString("artikel"));
				s.setSummary(bestFragment);
				s.setUrl(rs.getString("url"));
				s.setUstadz(rs.getString("ustadz"));
				s.setOffline(rs.getString("offline"));
				ls.add(s);
				
			}
			if(ls.size() == 0)
				return null;
			return ls;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	
	public List<SearchResult> searchDBForKeyword(String keyword,String source,  int start, int size){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		if(size == 0 || start < 0)
			return null;
		try{
			String keyword_artikel = cleanStopWord(keyword);
			keyword_artikel = cleanPunctuation(keyword_artikel);
			keyword_artikel = expandQuery(keyword_artikel, conn);
			
			String sql = "{call searchKeywordOneSourcePaging(?,?,?)}";
			CallableStatement ps = conn.prepareCall(sql);
			ps.setString(1, keyword_artikel);
			ps.setString(2, source);
			ps.setInt(3, start);
			
			rs = ps.executeQuery();
			List<SearchResult> ls = new ArrayList<SearchResult>();
			while(rs.next()){
				SearchResult s = new SearchResult();
				s.setId(rs.getInt("id"));
				s.setTitle(rs.getString("judul"));
				s.setSource(rs.getString("sumber"));
				//String artikel = FragmentUtil.getBestSummaryFragment(keyword,FragmentUtil.getBestSummaryFragment(keyword,rs.getString("ringkasan")));
				String bestFragment = FragmentUtil.getBestFragments(keyword_artikel, s.getTitle() + "\n"+ rs.getString("artikel"));
				s.setSummary(bestFragment);
				s.setUrl(rs.getString("url"));
				s.setUstadz(rs.getString("ustadz"));
				s.setOffline(rs.getString("offline"));
				ls.add(s);
			}
			if(ls.size() == 0)
				return null;
			return ls;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	public List<SearchResult> searchSimilarQuestion(String keyword,int qid){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		try{
			String keyword_artikel = cleanStopWord(keyword);
			keyword_artikel = expandQuery(keyword_artikel, conn);
			keyword_artikel = cleanPunctuation(keyword_artikel);
			String sql = "{call searchSimilarResult(?)}";
			CallableStatement ps = conn.prepareCall(sql);
			ps.setInt(1, qid);
			
			rs = ps.executeQuery();
			List<SearchResult> ls = new ArrayList<SearchResult>();
			while(rs.next()){
				SearchResult s = new SearchResult();
				s.setId(rs.getInt("id"));
				s.setTitle(rs.getString("judul"));
				s.setSource(rs.getString("sumber"));				
				String bestFragment = FragmentUtil.getBestFragments(keyword_artikel, s.getTitle() + "\n"+ rs.getString("artikel"));
				s.setSummary(bestFragment);
				s.setUrl(rs.getString("url"));
				s.setUstadz(rs.getString("ustadz"));
				s.setOffline(rs.getString("offline"));
				ls.add(s);
			}
			if(ls.size() == 0)
				return null;
			return ls;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	
	public List<SearchResult> searchSimilarQuestion(String keyword,int qid, int start, int size){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		try{
			String keyword_artikel = cleanStopWord(keyword);
			keyword_artikel = cleanPunctuation(keyword_artikel);
			keyword_artikel = expandQuery(keyword_artikel, conn);
			String sql = "{call searchSimilarResultPaging(?,?)}";
			CallableStatement ps = conn.prepareCall(sql);
			ps.setInt(1, qid);
			ps.setInt(2, start);
			rs = ps.executeQuery();
			List<SearchResult> ls = new ArrayList<SearchResult>();
			while(rs.next()){
				SearchResult s = new SearchResult();
				s.setId(rs.getInt("id"));
				s.setTitle(rs.getString("judul"));
				s.setSource(rs.getString("sumber"));				
				String bestFragment = FragmentUtil.getBestFragments(keyword_artikel, s.getTitle() + "\n"+ rs.getString("artikel"));
				s.setSummary(bestFragment);
				s.setUrl(rs.getString("url"));
				s.setUstadz(rs.getString("ustadz"));
				s.setOffline(rs.getString("offline"));
				ls.add(s);
			}
			if(ls.size() == 0)
				return null;
			return ls;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	public List<String> getSourcesForResult(String keyword){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;

		try{
			keyword = cleanStopWord(keyword);
			keyword = cleanPunctuation(keyword);
			keyword = expandQuery(keyword, conn);
			String sql = "{call getSourcesForResult(?)}";//+Constant.MAX_HIT_NUM;
			CallableStatement ps = conn.prepareCall(sql);
			ps.setString(1, keyword);
		
			rs = ps.executeQuery();
			List<String> ls = new ArrayList<String>();
			while(rs.next()){
				String s = rs.getString("sumber");
				ls.add(s);
			}
			if(ls.size() == 0)
				return null;
			return ls;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	
	public int getTotalCount(String keyword){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		try{
			keyword = cleanStopWord(keyword);
			keyword = cleanPunctuation(keyword);
			keyword = expandQuery(keyword, conn);
			//String sql =  "SELECT COUNT(id) sumber FROM ustadzmenjawabdb_prod WHERE MATCH (judul,artikel) AGAINST (?) limit "+Constant.MAX_HIT_NUM;
			String sql = "{call getTotalCount(?)}";
			CallableStatement ps = conn.prepareCall(sql);
			ps.setString(1, keyword);
			rs = ps.executeQuery();
			int c = 0;
			while(rs.next()){
				c = rs.getInt(1);
			}
			return c;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return 0;
	}
	
	public int getTotalCount(String keyword, String source){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		try{
			keyword = cleanStopWord(keyword);
			keyword = cleanPunctuation(keyword);
			keyword = expandQuery(keyword, conn);
			//String sql =  "SELECT COUNT(id) sumber FROM ustadzmenjawabdb_prod WHERE MATCH (judul,artikel) AGAINST (?) where sumber = ? limit "+Constant.MAX_HIT_NUM;
			String sql = "{call getTotalCountOneSource(?,?)}";
			CallableStatement ps = conn.prepareCall(sql);
			ps.setString(1, keyword);
			ps.setString(2, source);
			rs = ps.executeQuery();
			int c = 0;
			while(rs.next()){
				c = rs.getInt(1);
			}
			return c;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return 0;
	}
	public int getCountSimilarResult(int qid){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		try{
			String sql = "{call getCountSimilarResult(?)}";
			CallableStatement ps = conn.prepareCall(sql);
			ps.setInt(1, qid);
			rs = ps.executeQuery();
			int c = 0;
			while(rs.next()){
				c = rs.getInt(1);
			}
			return c;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return 0;
	}
	public String getQuestion(int id){
		Connection conn = DBConnFactory.getConnection();
		ResultSet rs = null;
		try{
			String sql = "Select pertanyaan from ustadzmenjawabdb_prod where id = ? ";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()){
				return rs.getString(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnFactory.closeConnection(conn);
		}
		return null;
	}
	protected String expandQuery(String text, Connection conn){
		/*try {
			if(conn == null || conn.isClosed())
				return null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(text == null)
			return null;
		PreparedStatement ps = null;
		try{
			String ret = "";
			String sql = "Select daftar_sinonim from sinonim where term = ?";
			ps = conn.prepareStatement(sql);
			String[] terms = text.split(" ");
			ResultSet rs = null;
			for(String term : terms){
				term = term.trim();
				ps.setString(1,term);
				rs = ps.executeQuery();
				String sinonim = null;
				while(rs.next()){
					sinonim = rs.getString(1);
				}
				rs.close();
				if(sinonim != null){
					if(sinonim.length() > 0)
						sinonim = "( "+sinonim.replace(";"," OR ")+" )";	
					ret += sinonim+ " ";
				}else{
					ret += term + " ";
				}
			}
			//System.out.println("Expand Query: " + ret.trim());
			return ret.trim();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}*/
		return text.trim();
	}
	
	protected void insertUserQuery(String keyword,String ipAddr, Connection conn){
		try {
			if(conn == null || conn.isClosed())
				return ;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(keyword == null)
			return ;
		PreparedStatement ps = null;
		try{
			String sql = "insert into keyword_user(kata_pencarian, ip_addr, waktu_pencarian) values(?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, keyword);
			ps.setString(2, ipAddr);
			ps.setTimestamp(3, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	protected static String cleanStopWord(String keyword){
			if(keyword == null)
				return null;
			String[] terms = keyword.toLowerCase().trim().split(" ");
			String ret = "";
			for(String term : terms){
				if(term.length() > 0){
					if(!stopWord.contains(term.trim())){
						ret += term + " ";
					}
				}
			}
			if(ret.length() > 0){
				//System.out.println("Clean Stopword: " +ret);
				return ret.trim();
			}
			return null;
	}
	
	protected static String cleanPunctuation(String keyword){
		if(keyword == null)
			return null;
		return keyword
		.replace("?", " ")
		.replace("!", " ")
		.replace(",", " ")
		.replace(".", " ")
		.replace("*", " ")
		.replace(":", " ")
		.replace("/", " / ")
		.replace("\\", " ");
	}
	
	private static void loadStopWord(){
		Connection conn = DBConnFactory.getConnection();
		try {
			String sql = "Select term from stopword";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if(stopWord == null)
				stopWord = new HashSet<String>();
			while(rs.next()){
				stopWord.add(rs.getString(1));
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	/*
	private Hashtable<String,Double> getWeight(Connection conn){
		ResultSet rs =null;
		PreparedStatement ps = null;
		try{
			String sql = "Select judul,artikel,cutoff from weight";
			 ps = conn.prepareStatement(sql);
			 rs = ps.executeQuery();
			Hashtable <String,Double> weight = new Hashtable<String,Double>();
			while(rs.next()){
				weight.put("judul", rs.getDouble(1));
				weight.put("artikel",rs.getDouble(2));
				weight.put("limit",rs.getDouble(3));
			}
			return weight;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null && !rs.isClosed())
					rs.close();
				if(ps != null && !ps.isClosed())
					ps.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return null;
	}
	*/
	
	
	
	
	
}
